﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Windows.Forms;
using System.Globalization;
using Excel;
using SpectationClient.DataBaseDescription;
using SpectationClient.Stuff;
using System.IO;

namespace SpectationClient.GUI
{
    public partial class SubReadMessprotokollGFZXLS : Form{
        public event EventHandler Ready2ReadValues;
        DataSet DS;
        List<String> sheetlist;
        ErrorList EL;

        DataTable NUMBERSTYLES;
        NumberStyles numberstyle;
        CultureInfo cultureinfo;

        public SubReadMessprotokollGFZXLS(){
            InitializeComponent();

            this.Icon = Resources.Icon;
            DS = new DataSet();
            EL = new ErrorList();
            EL.ErrorAdded += new EventHandler(EL_ErrorAdded);
            EL.ErrorListIsEmpty += new EventHandler(EL_ErrorListIsEmpty);
            sheetlist = new List<string>();
            NUMBERSTYLES = new DataTable();
            NUMBERSTYLES.Columns.Add("DISPLAY", typeof(String));
            NUMBERSTYLES.Columns.Add("STYLE", typeof(NumberStyles));
            NUMBERSTYLES.Columns.Add("CULTURE", typeof(CultureInfo));
            NUMBERSTYLES.Rows.Add(new Object[] { "',' Komma", NumberStyles.AllowDecimalPoint, CultureInfo.CreateSpecificCulture("de-DE") });
            NUMBERSTYLES.Rows.Add(new Object[] { "'.' Punkt", NumberStyles.None, CultureInfo.CreateSpecificCulture("de-DE") });
            cbDecimalPoints.DataSource = NUMBERSTYLES;
            cbDecimalPoints.DisplayMember = "DISPLAY";
            cbDecimalPoints_SelectedIndexChanged(null, null);

            DGV.DataSourceChanged += new EventHandler(DGV_DataSourceChanged);
            DGV.CellFormatting += new DataGridViewCellFormattingEventHandler(DGV_CellFormatting);
            
        }

        void EL_ErrorListIsEmpty(object sender, EventArgs e) {
            //DGVDockPanel.ForeColor = Color.Black;
            //DGVDockPanel.BackColor = Color.Black;
        }

        void EL_ErrorAdded(object sender, EventArgs e) {


            //DGVDockPanel.ForeColor = Color.Red;
            //DGVDockPanel.BackColor = Color.Red;
        }

        void DGV_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e) {
            DataGridViewCell cell = this.DGV[e.ColumnIndex, e.RowIndex];
            
        }

        void DGV_DataSourceChanged(object sender, EventArgs e) {
            sheetlist_cb.Enabled = btReady2Read.Enabled = DGV.DataSource != null && DS.Tables.Count > 0;
            if(DGV.DataSource != null) {
                for(int i = 1; i <= DGV.RowCount; i++) {
                    DGV.Rows[i - 1].HeaderCell.Value = i.ToString();
                }
                DGV.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders;
            }
        }

        private void btnOpen_Click(object sender, EventArgs e){
            if(OFD.ShowDialog() == DialogResult.OK && File.Exists(OFD.FileName)) {
                this.DS = this.readMessprotokoll(OFD.FileName);
                if(this.DS != null && DS.Tables.Count > 0) {
                    this.sheetlist = new List<string>();
                    foreach(DataTable dt in DS.Tables) {
                        this.sheetlist.Add(dt.TableName);
                    }
                    this.sheetlist_cb.DataSource = sheetlist;
                    DGV.DataSource = DS.Tables[0];
                    this.sheetlist_label.Enabled = true;
                }
            } else {
                this.DGV.DataSource = null;
                this.DGV.Refresh();
                this.DS = null;
                this.sheetlist_label.Enabled = false;

            }
        }

        private DataSet readMessprotokoll(String path) {
           
            FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader ER = null;
            if(Path.GetExtension(path).Equals(".xls", StringComparison.OrdinalIgnoreCase)) {

                ER = ExcelReaderFactory.CreateBinaryReader(stream);
                DataSet ds = ER.AsDataSet();
                //String temp = "";
            } else if(Path.GetExtension(path).Equals(".xlsx", StringComparison.OrdinalIgnoreCase)) {
                ER = ExcelReaderFactory.CreateOpenXmlReader(stream);
                DataSet ds = ER.AsDataSet();

                MessageBox.Show("Excel 2010 Files (*.xlsx) are not supported");
                return ds;

            } else { MessageBox.Show("File type unknown"); return null; }

            DataSet DS = new DataSet();
            try {
                int sheets = ER.ResultsCount;
                int sheet_no = 0;
                while(ER.NextResult()) {
                    DataTable dt_sheet = new DataTable(ER.Name);//Sheet_Name
                    int row = 0;
                    while(ER.Read()) {
                        if(dt_sheet.Columns.Count < ER.FieldCount) {
                            int diff = ER.FieldCount - dt_sheet.Columns.Count;
                            for(int i = 0; i < diff; i++) dt_sheet.Columns.Add(getColumnName(i + 1), typeof(String));
                        }
                        DataRow dr = dt_sheet.NewRow();
                        String str_cell;
                        for(int i = 0; i < ER.FieldCount; i++) {
                            str_cell = ER.GetString(i);
                            if(str_cell != null && str_cell.Length > 0) {
                                try {
                                    //Exceptions, where the Excel number represents a Date or Time stamp
                                    if(row == 3 && i == 1) {
                                        dr[i] = ER.GetDateTime(i).Date.ToShortDateString();
                                    } else if((row == 4 || row == 5) && i == 2) {
                                        dr[i] = ER.GetDateTime(i).TimeOfDay.ToString();
                                    } else {
                                        dr[i] = ER.GetString(i);
                                    }
                                } catch(Exception e) {
                                    Console.WriteLine("SubReadMessprotokollGFZXLS:readMessprotokoll(String path): {0}", e.Message);
                                    dr[i] = ER.GetString(i);
                                }
                            } else {
                                dr[i] = null;
                            }
                        }
                        dt_sheet.Rows.Add(dr);
                        row++;
                    }
                    //Check for proper XLS
                    //Console.WriteLine(dt_sheet.Rows[0].ItemArray[0].ToString());
                    if(dt_sheet.Rows.Count >= 20 && 
                       dt_sheet.Rows[0].ItemArray[0].ToString() == "Titeldaten" &&
                       dt_sheet.Rows[19].ItemArray[0].ToString() == "Messung") {
                        DS.Tables.Add(dt_sheet);
                        sheet_no++;
                    }
                };
                ER.Close();
                stream.Close();
            } catch(Exception ex) {
                MessageBox.Show(TextHelper.Exception2String(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return DS;
        }

        private bool TryParse(String text, out Single number){
            bool a = Single.TryParse(text, NumberStyles.AllowDecimalPoint
                , CultureInfo.CreateSpecificCulture("es-ES")
                , out number);
            if(a) return a;
            bool b = Single.TryParse(text, NumberStyles.AllowDecimalPoint
                , CultureInfo.CreateSpecificCulture("en-GB")
                , out number);
            if(b) return b;
            return false;
        }

        private bool TryParse(String text, out Double number) {
            bool a = Double.TryParse(text, NumberStyles.AllowDecimalPoint
                    , CultureInfo.CreateSpecificCulture("de-DE")
                    , out number);
            if(a) return a;
            bool b = Double.TryParse(text, NumberStyles.AllowDecimalPoint
                    , CultureInfo.CreateSpecificCulture("en-EN")
                    , out number);
            if(b) return b;
            return false;
        }

        private void cbSheets_SelectedIndexChanged(object sender, EventArgs e) {
            if(DS != null) DGV.DataSource = DS.Tables[sheetlist_cb.SelectedIndex];
            RTB.Clear();
            EL.removeAllErrors();
        }

        private void btOK_Click(object sender, EventArgs e) {
            //Take values from XLS
            RTB.Clear();
            if(this.Ready2ReadValues != null) Ready2ReadValues(this, new EventArgs());
            
        }

        private int getColumnNumber(string name){
            int number = 0;
            int pow = 1;
            for (int i = name.Length - 1; i >= 0; i--){
                number += (name[i] - 'A' + 1) * pow;
                pow *= 26;
            }
            return number - 1;
        }

        private string getColumnName(int count){
            return new string((char)('A'+(((count-1) % 26))), ((count-1) / 26) + 1);
        }


        public bool hasXLSvalue(string col, int row) { 
            int c = this.getColumnNumber(col);
            return hasXLSvalue(c, row - 1);
        }

        public bool hasXLSvalue(int col, int row) {
            return DGV[col, row].Value.ToString().Length > 0;
        }
        

      
        public Object getValue(String column, int row, String VariabelName) {
            return this.getValue(column, row, typeof(String), VariabelName, false);
        }
        public Object getValue(String column, int row, Type VariableType, String VariabelName) {
            return this.getValue(column, row, VariableType, VariabelName, false);
        }
        
        public Object getValue(String column, int row, String VariabelName, bool isRequired) {
            return this.getValue(column, row, typeof(String), VariabelName, isRequired);
        }
        public Object getValue(String column, int row, Type VariableType, String VariabelName, bool isRequired) {
            bool hasValue = false;
            DataGridViewCell cell = null;
            String stringValue = null;
            String infoText = null;
            MessageType messageType = MessageType.error;
            Object finalValue = null;
            if(DGV.RowCount < row || !DGV.Columns.Contains(column)) {
                infoText = "Zelle existiert nicht.";
            } else {
                cell = DGV[column, row - 1];
                stringValue = cell.Value.ToString().Trim();
                hasValue = stringValue.Length > 0;
                if(!hasValue) {
                    infoText = "ohne Angabe";
                }else if (VariableType == null) {
                    finalValue = stringValue;
                } else {
                    try {
                        finalValue = Convert.ChangeType(stringValue, VariableType);
                    } catch(Exception ex) {
                        infoText = String.Format("kann nicht als Datentyp \"{0}\" eingelesen werden ({1}).",
                            VariableType.Name, ex.Message);
                        hasValue = false;
                        finalValue = null;
                    }
                }
            }

            if(!hasValue) {
                messageType = (isRequired || cell == null) ? MessageType.error : MessageType.info;
                this.setMessage(column, row, VariabelName, infoText, messageType);
            }
            if(hasValue || (!isRequired && cell != null)) this.EL.removeError(cell);
            return finalValue;
        }

        public bool setValue(Object form, String column, int row, String VariabelName) {
            return this.setValue(form, column, row, typeof(String), VariabelName, false);
        }

        public bool setValue(Object form, String column, int row, String VariabelName, bool isRequired) {
            return this.setValue(form, column, row, typeof(String), VariabelName, isRequired);
        }
        public bool setValue(Object form, String column, int row, Type VariableType, String VariabelName) {
            return this.setValue(form, column, row, VariableType, VariabelName, false);
        }
        public bool setValue(Object form, String column, int row, Type VariableType, String VariabelName, bool isRequired) {
            Object finalValue = this.getValue(column, row, VariableType, VariabelName, isRequired);
            if(finalValue != null) {
                //Assign value to form element
                TextBox tb = form as TextBox;
                if(tb != null) {
                    tb.Text = finalValue.ToString();
                }


                bool case_sensitive = false;
                ComboBox cb = form as ComboBox;
                if(cb != null) {
                    Char[] sep = new char[] { ',', ';', ' ' };
                    String[] s_part = finalValue.ToString().Split(sep);
                    //check the value members
                    if(cb.DataSource != null && cb.DataSource.GetType() == typeof(DataTable)) {
                        DataTable dt = (DataTable)cb.DataSource;
                        int[] matches = new int[dt.Rows.Count];
                        String[] val_disp, val_val = null;
                        int n_match, n_max_match, i_max_match;
                        n_match = n_max_match = i_max_match = 0;
                        for(int i = 0; i < matches.Length; i++) {
                            val_disp = dt.Rows[i][cb.DisplayMember].ToString().Trim().Split(sep);
                            val_val = dt.Rows[i][cb.ValueMember].ToString().Trim().Split(sep);
                            n_match = Math.Max(this.numberStringMatches(s_part, val_disp, case_sensitive),
                                               this.numberStringMatches(s_part, val_val, case_sensitive));
                            if(n_match > n_max_match) {
                                i_max_match = i;
                                n_max_match = n_match;
                            }
                        }
                        //use the item with most matches, if there was attributes least one match!
                        if(n_max_match > 0) {
                            cb.SelectedIndex = i_max_match;
                        } else {
                            cb.SelectedIndex = -1;
                            String errorMessage = String.Format("Wert \"{0}\" steht nicht zur Auswahl.", finalValue);
                            this.setMessage(column, row, VariabelName, errorMessage, MessageType.error);
                        }
                    } else {
                        cb.SelectedIndex = -1;
                    }


                }



                ListBox lb = form as ListBox;
                if(lb != null) throw new NotImplementedException();

                RichTextBox rtb = form as RichTextBox;
                if(rtb != null) rtb.Text += finalValue.ToString();

                DateTimePicker dtp = form as DateTimePicker;
                if(dtp != null) {
                    dtp.Checked = true;
                    dtp.Value = Convert.ToDateTime(finalValue);
                }

            } else { 
            //What happens in case of fail? 
            //Error / Info Message is already set by getData Methode
            //Now -> clear all
                ComboBox cb = form as ComboBox;
                if(cb != null) cb.SelectedIndex = -1;

                ListBox lb = form as ListBox;
                if(lb != null) throw new NotImplementedException();

                TextBox tb = form as TextBox;
                if(tb != null) tb.Clear();

                RichTextBox rtb = form as RichTextBox;
                if(rtb != null) rtb.Clear();

                DateTimePicker dtp = form as DateTimePicker;
                if(dtp != null) {
                    dtp.Value = DateTime.Today;
                    dtp.Checked = false;
                }
            }
            return finalValue != null;
        }

        private int numberStringMatches(String[] a, String[] b, bool case_sensitive) {
            int n = 0;
            for(int i = 0; i < a.Length; i++) {
                for(int j = 0; j < b.Length; j++) {
                    if(case_sensitive) {
                        if(String.Equals(a[i], b[j], StringComparison.Ordinal)) n++;
                    } else {
                        if(String.Equals(a[i], b[j], StringComparison.OrdinalIgnoreCase)) n++;
                    }
                }
            }
            return n;
        }


        public enum  MessageType : byte {
            error = 0,
            info = 1
        }

        public void setMessage(string msg) {
            this.setMessage(null, -1, null, msg, MessageType.error);
        }

        public void setMessage(string msg, MessageType messageType){
            this.setMessage(null, -1, msg, null, messageType);
        }

   
        public void setMessage(String column, int row, string msg) {
            this.setMessage(column, row, null, msg, MessageType.error);
        }
        public void setMessage(String column, int row, string VariableName, string msg) {
            this.setMessage(column, row, VariableName, msg, MessageType.error);
        }
        public void setMessage(String column, int row, String VariableName, string msg, MessageType messageType) {
            if(msg == null || msg.Trim().Length == 0) return;


            Color color = Color.Black;
            switch(messageType){
                case MessageType.error : color= Color.Red; break;
            }
            msg += Environment.NewLine;
            //include variable name
            if(VariableName != null) msg = String.Format("\"{0}\" {1}", VariableName, msg);
            
            //include colum/iPoint information
            if(column != null){
                msg = String.Format("{0}{1}:{2}", column, row, msg);
            }

            //is it an error? mark cell
            if(messageType == MessageType.error && this.DGV.Columns.Contains(column) && this.DGV.RowCount >= row) {
                EL.addError(DGV[column, row - 1], msg);
            }

            RTB.AppendText(msg);
            RTB.Select(RTB.Text.Length - (msg.Length - 1), msg.Length);
            RTB.SelectionColor = color;
        }

        private void btCancel_Click(object sender, EventArgs e) {
            this.Dispose();
        }

        private void cbDecimalPoints_SelectedIndexChanged(object sender, EventArgs e) {
            DataRow r = ((DataRowView)cbDecimalPoints.SelectedItem).Row;
            this.cultureinfo = (CultureInfo)r["CULTURE"];
            this.numberstyle = (NumberStyles)r["STYLE"];
        }


       
    }
}
